import sys
import string
import sqlite3
from time import time
import logging

class CantConnect(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value) 

class OperationalError(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value) 

class ExecpectedError(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value)

class NoExistHashError(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value)

class NoExistKeyError(Exception):
    def __init__(self, value):
        self.value = value
    def __str__(self):
        return repr(self.value)


class DBase:
    _db = None
    
    def __init__(self, dbfile):
        try:
            self._db = sqlite3.connect(dbfile)
            self._db.row_factory = sqlite3.Row
        except NameError as e:
            raise CantConnect(e)
        except:
            raise CantConnect(sys.exc_info()[0])
    
    def __del__(self):
        self._db.commit()
        self._db.close()

    def initDbase(self):
        schema = (
            '''CREATE TABLE IF NOT EXISTS configs (key TEXT, value TEXT, UNIQUE (key) ON CONFLICT ABORT);''',
            '''CREATE TABLE IF NOT EXISTS file_descr (sha256Hash TEXT REFERENCES files(sha256Hash) ON UPDATE CASCADE, descr TEXT, UNIQUE(sha256Hash) ON CONFLICT REPLACE);''',
            '''CREATE TABLE IF NOT EXISTS file_rating (sha256Hash TEXT REFERENCES files(sha256Hash) ON UPDATE CASCADE, rating INT, UNIQUE(sha256Hash) ON CONFLICT REPLACE);''',
            '''CREATE TABLE IF NOT EXISTS file_stats (sha256Hash TEXT REFERENCES files(sha256Hash) ON UPDATE CASCADE, directory TEXT, filename TEXT, filesize INT, atime INT, mtime INT, chtime INT, UNIQUE (sha256Hash) ON CONFLICT REPLACE);''',
            '''CREATE TABLE IF NOT EXISTS files (sha256Hash TEXT, commitDate INT, mediaType TEXT, fileType TEXT, UNIQUE(sha256Hash) ON CONFLICT REPLACE);''',
            '''CREATE TABLE IF NOT EXISTS s3 (sha256Hash TEXT, etag TEXT, UNIQUE (sha256Hash, etag) ON CONFLICT REPLACE);''',
            '''CREATE TABLE IF NOT EXISTS tagFileLink (tag INT TEXT REFERENCES tags(rowid) ON UPDATE CASCADE, sha256Hash TEXT REFERENCES files(sha256Hash) ON UPDATE CASCADE, UNIQUE(tag, sha256Hash) ON CONFLICT REPLACE);''',
            '''CREATE TABLE IF NOT EXISTS tags (descr TEXT, UNIQUE(descr) ON CONFLICT IGNORE);''',
            '''CREATE TABLE IF NOT EXISTS video_durations (sha256Hash TEXT REFERENCES files(sha256Hash) ON UPDATE CASCADE, duration TEXT, UNIQUE(sha256Hash) ON CONFLICT REPLACE);''',
            '''CREATE TABLE IF NOT EXISTS thumbnails (sha256Hash TEXT, thumbSha256Hash TEXT, filesize INTEGER, thumbSize INTEGER, UNIQUE(sha256Hash, thumbSha256Hash) ON CONFLICT REPLACE);''',
            '''CREATE TABLE IF NOT EXISTS users (username TEXT, secret TEXT, UNIQUE (username) ON CONFLICT ABORT);''',
            '''CREATE TABLE IF NOT EXISTS psk (username TEXT, psk TEXT, timestamp TEXT, UNIQUE (username) ON CONFLICT REPLACE);''',
            '''CREATE INDEX IF NOT EXISTS file_descr_sha256Hash on file_descr (sha256Hash);''',
            '''CREATE INDEX IF NOT EXISTS file_rating_sha256Hash on file_rating (sha256Hash);''',
            '''CREATE INDEX IF NOT EXISTS files_fileType ON files(fileType);''',
            '''CREATE INDEX IF NOT EXISTS files_mediaType ON files(mediaType);''',
            '''CREATE INDEX IF NOT EXISTS files_sha256Hash on files (sha256Hash);''',
            '''CREATE INDEX IF NOT EXISTS tags_descr on tags (descr);''',
            '''CREATE INDEX IF NOT EXISTS video_durations_sha256Hash on video_durations (sha256Hash);''')
        for entry in schema:
            try:
                self._db.execute(entry)
            except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
                raise OperationalError(e)
            except:
                logging.critical((sys.exc_info()))
                return False
        self._db.commit()
        return True

    def fetchUserSecret(self, username):
        if username is None:
            logging.debug("Username is None")
            return None
        try:
            for row in self._db.execute('''SELECT secret FROM users WHERE username = ?''', (username,)):
                secret = row["secret"]
                logging.debug("Fetched secret ({})".format(secret))
                return secret
        except:
            raise

    def fetchPSK(self, username):
        if username is None:
            logging.debug("Username is None")
            return None
        try:
            for row in self._db.execute('''SELECT psk, timestamp FROM psk WHERE username = ?''', (username,)):
                secret = row["psk"]
                timestamp = row["timestamp"]
                logging.debug("Fetched secret ({}) timestamp ({})".format(secret, timestamp))
                return {"secret": secret, "timestamp": timestamp}
        except IndexError:
            return None
        except:
            raise

    def updatePSK(self, username, psk):
        try:
            commitDate = int(time())
            self._db.execute('''INSERT INTO psk (username, psk, timestamp) VALUES (?, ?, ?);''', (username, psk, commitDate))
            logging.debug("Updated/Inserted PSK")
        except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
            raise OperationalError(e)
        except:
            raise

    def newImageThumbnail(self, origSha256Hash, newSha256Hash, filesize, thumbsize = 3):
        try:
            self._db.execute('''INSERT INTO thumbnails (sha256Hash, thumbSha256Hash, filesize, thumbSize) VALUES (?, ?, ?, ?)''', 
                             (origSha256Hash, newSha256Hash, filesize, thumbsize))
        except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
            raise OperationalError(e)
        except:
            raise

    def getThumbnail(self, sha256Hash, thumbSize):
        try:
            for row in self._db.execute('''SELECT thumbSha256Hash, filesize FROM thumbnails WHERE sha256Hash = ? AND thumbsize = ?''', (sha256Hash, thumbSize)):
                return {"filesize": row["filesize"], "sha256Hash": row["thumbSha256Hash"], "fileType": "png"}
        except IndexError:
            return None
        except:
            raise

    def getThumbnailAllSize(self, sha256Hash):
        media = []
        try:
            for row in self._db.execute('''SELECT thumbSha256Hash, filesize, thumbsize FROM thumbnails WHERE sha256Hash = ?''', (sha256Hash,)):
                media.append({"filesize": row["filesize"], "sha256Hash": row["thumbSha256Hash"], "thumbsize": row["thumbsize"], "fileType": "png"})
        except IndexError:
            return None
        except:
            raise
        return media

    def rmTumbnail(self, sha256Hash):
        try:
            logging.debug("Removing thumbnail from db ({})".format(sha256Hash))
            self._db.execute('''DELETE FROM thumbnails WHERE thumbSha256Hash = ?''', (sha256Hash,))
            return True
        except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
            raise OperationalError(e)
        except:
            raise
    
    def getAllThumbnails(self):
        try:
            logging.debug("Fetching All Thumbnails")
            thumbs = []
            for row in self._db.execute('''SELECT thumbSha256Hash, filesize FROM thumbnails'''):
                thumbs.append({"filesize": row["filesize"], "sha256Hash": row["thumbSha256Hash"], "fileType": "png"})
            return thumbs
        except IndexError:
            return None
        except:
            raise

    def newMedia(self, sha256Hash, mediaType, fileType, tags, directory, filename, filesize, atime, mtime, chtime):
        try:
        
            commitDate = int(time())
            self._db.execute('''INSERT INTO files (sha256Hash, commitDate, mediaType, fileType) VALUES (?, ?, ?, ?)''', (sha256Hash, commitDate, mediaType, fileType,))
            self._db.execute('''INSERT INTO file_stats (sha256Hash, directory, filename, filesize, atime, mtime, chtime) VALUES (?, ?, ?, ?, ?, ?, ?)''', (sha256Hash, directory, filename, filesize, atime, mtime, chtime,))
            for tag in tags:
                self._db.execute('''INSERT INTO tags (descr) VALUES (?)''', (tag,))
                for row in self._db.execute('''SELECT rowid FROM tags WHERE descr = ?''', (tag,)):
                    rowid = row["rowid"]
                self._db.execute('''INSERT INTO tagFileLink (tag, sha256Hash) VALUES (?, ?)''', (rowid, sha256Hash))
        except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
            raise OperationalError(e)
        except:
            raise
        return True

    def lsMediaTypes(self):
        try:
            mediaTypeList = []
            for row in self._db.execute('''SELECT DISTINCT(mediaType) AS types FROM files'''):
                mediaTypeList.append({"type": row["types"]}) 
        except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
            raise OperationalError(e)
        except:
            raise ExecpectedError("Unexpected error:", sys.exc_info()[0])
        return mediaTypeList

    def lsFileTypes(self):
        try:
            mediaTypeList = []
            for row in self._db.execute('''SELECT DISTINCT(fileType) AS types FROM files'''):
                mediaTypeList.append({"type": row["types"]}) 
        except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
            raise OperationalError(e)
        except:
            raise ExecpectedError("Unexpected error:", sys.exc_info()[0])
        return mediaTypeList
        
    def mediaInfo(self, sha256Hash = None, tagLimitList = None, mediaType = None, search = None, fileType = None):
        mediaList = []
        ## If one hash is provided...
        if sha256Hash is not None:
            query = "SELECT f.rowid, f.sha256Hash, f.commitDate, f.mediaType, f.fileType, fs.directory, \
                fs.filename, fs.filesize, fs.atime, fs.mtime, fs.chtime, d.descr, r.rating, s3.sha256Hash as s3, \
                (SELECT GROUP_CONCAT(tags.descr, '!|!') FROM tagFileLink JOIN tags ON tags.rowid = tagFileLink.tag \
                WHERE tagFileLink.sha256Hash=f.sha256Hash) AS tagList FROM files f JOIN file_stats fs ON \
                fs.sha256Hash = f.sha256Hash LEFT OUTER JOIN file_descr d ON d.sha256Hash = f.sha256Hash \
                LEFT OUTER JOIN file_rating r ON r.sha256Hash = f.sha256Hash LEFT OUTER JOIN s3 ON \
                s3.sha256Hash = f.sha256Hash WHERE \
                f.sha256Hash = '{}'".format(sha256Hash)
        elif tagLimitList == "--noTags":
            mediaLimit = None
            if mediaType is not None:
                mediaLimit = "f.mediaType = '{}'".format(mediaType)

            fileLimit = None
            if fileType is not None:
                fileLimit = "f.fileTYpe = '{}'".format(fileType)

            searchStr = None
            if search is not None:
                searchStr = "fs.filename LIKE '%{}%' OR d.descr LIKE '%{}%'".format(search, search)

            wherePart = ""
            for limit in [mediaLimit, fileLimit, searchStr]:
                if limit is None:
                    continue
                wherePart = "{} AND {}".format(wherePart, limit)
            if wherePart != "":
                wherePart = " {}".format(wherePart[5:])
            ## Comlex search; but no tag complications
            query = "SELECT f.rowid, f.sha256Hash, f.commitDate, f.mediaType, f.fileType, fs.directory, fs.filename, \
                fs.filesize, fs.atime, fs.mtime, fs.chtime, d.descr, r.rating, s3.sha256Hash as s3, FROM files f JOIN \
                file_stats fs ON fs.sha256Hash = f.sha256Hash LEFT OUTER JOIN file_descr d ON d.sha256Hash = \
                f.sha256Hash LEFT OUTER JOIN file_rating r ON r.sha256Hash = f.sha256Hash LEFT OUTER JOIN s3 \
                ON s3.sha256Hash = f.sha256Hash LEFT OUTER JOIN webPics wp ON wp.sha256Hash = f.sha256Hash \
                WHERE f.sha256Hash NOT IN (SELECT sha256Hash from tagFileLink) {}".format(wherePart)
        elif tagLimitList is not None:
            tagCount = len(tagLimitList)
            tagIdList = ""
            for tag in tagLimitList:
                tagIdList = "{},{}".format(tagIdList, self.getTagID(tag))
            tagIdList = tagIdList[1:]

            mediaLimit = ""
            if mediaType is not None:
                mediaLimit = "f.mediaType = '{}'".format(mediaType)

            fileLimit = ""
            if fileType is not None:
                fileLimit = "f.fileType = '{}'".format(fileType)

            searchStr = ""
            if search is not None:
                searchStr = "fs.filename LIKE '%{}%' OR d.descr LIKE'%{}%'".format(search, search)
            wherePart = ""
            for limit in [mediaLimit, fileLimit, searchStr]:
                if limit == "":
                    continue
                wherePart = "{}{} AND".format(wherePart, limit)
            if wherePart != "":
                wherePart = "WHERE {}".format(wherePart[:-4])

            query = "SELECT f.rowid, f.sha256Hash, f.commitDate, f.mediaType, f.fileType, fs.directory, fs.filename, \
                fs.filesize, fs.atime, fs.mtime, fs.chtime, d.descr, r.rating, s3.sha256Hash as s3, \
                (SELECT GROUP_CONCAT(tags.descr, '!|!') FROM tagFileLink JOIN tags ON tags.rowid = tagFileLink.tag \
                WHERE tagFileLink.sha256Hash=f.sha256Hash) AS tagList FROM files f JOIN file_stats fs ON f.sha256Hash \
                = fs.sha256Hash LEFT OUTER JOIN file_descr d on d.sha256Hash = f.sha256Hash LEFT OUTER JOIN file_rating \
                r ON r.sha256Hash = f.sha256Hash LEFT OUTER JOIN s3 ON s3.sha256Hash = f.sha256Hash JOIN \
                tagFileLink tl ON f.sha256Hash = tl.sha256Hash JOIN (SELECT sha256Hash FROM tagFileLink tl WHERE tl.tag IN \
                ({}) GROUP BY sha256Hash HAVING COUNT (tl.rowid) = {}) t2 on f.sha256Hash = t2.sha256Hash {} GROUP BY \
                f.sha256Hash".format(tagIdList, tagCount, wherePart)
        else:
            mediaLimit = None
            if mediaType is not None:
                mediaLimit = "f.mediaType = '{}'".format(mediaType)

            fileLimit = None
            if fileType is not None:
                fileLimit = "f.fileTYpe = '{}'".format(fileType)

            searchStr = None
            if search is not None:
                searchStr = "fs.filename LIKE '%{}%' OR d.descr LIKE '%{}%'".format(search, search)

            wherePart = ""
            for limit in [mediaLimit, fileLimit, searchStr]:
                if limit is None:
                    continue
                wherePart = "{} AND {}".format(wherePart, limit)
            if wherePart != "":
                wherePart = "WHERE {}".format(wherePart[5:])
            
            query = "SELECT f.rowid, f.sha256Hash, f.commitDate, f.mediaType, f.fileType, fs.directory, fs.filename, \
                fs.filesize, fs.atime, fs.mtime, fs.chtime, d.descr, r.rating, s3.sha256Hash as s3, (SELECT GROUP_CONCAT(tags.descr, '!|!') FROM \
                tagFileLink JOIN tags ON tags.rowid = tagFileLink.tag WHERE tagFileLink.sha256Hash=f.sha256Hash) AS \
                tagList FROM files f JOIN file_stats fs ON fs.sha256Hash = f.sha256Hash LEFT OUTER JOIN file_descr d \
                ON d.sha256Hash = f.sha256Hash LEFT OUTER JOIN file_rating r ON r.sha256Hash = f.sha256Hash LEFT OUTER \
                JOIN s3 ON s3.sha256Hash = f.sha256Hash {}".format(wherePart)

        try:
            #logging.debug("query: ({})".format(query))
            for row in self._db.execute(query):
                mediaList.append(
                    {"sha256Hash": row["sha256Hash"],
                    "commitDate": row["commitDate"],
                    "mediaType": row["mediaType"],
                    "fileType": row["fileType"],
                    "directory": row["directory"],
                    "filename": row["filename"],
                    "filesize": row["filesize"],
                    "atime": row["atime"],
                    "mtime": row["mtime"],
                    "chtime": row["chtime"],
                    "descr": row["descr"],
                    "rating": row["rating"],
                    "s3": row["s3"],
                    "tagList": string.split(row["tagList"], "!|!")})
            return mediaList
        except:
            raise

    def mediaRm(self, sha256Hash):
        try:
            self._db.execute('''DELETE FROM file_descr WHERE sha256Hash = ?''', (sha256Hash,))
            self._db.execute('''DELETE FROM file_rating WHERE sha256Hash = ?''', (sha256Hash,))
            self._db.execute('''DELETE FROM file_stats WHERE sha256Hash = ?''', (sha256Hash,))
            self._db.execute('''DELETE FROM files WHERE sha256Hash = ?''', (sha256Hash,))
            self._db.execute('''DELETE FROM tagFileLink WHERE sha256Hash = ?''', (sha256Hash,))
            self._db.execute('''DELETE FROM video_durations WHERE sha256Hash = ?''', (sha256Hash,))
        except:
            raise
        return True

    def mediaDescr(self, sha256Hash, descr = None):
        if descr is not None:
            self._db.execute('''REPLACE INTO file_descr (sha256Hash, descr) VALUES (?, ?)''', (sha256Hash, descr,))
        for row in self._db.execute('''SELECT descr FROM file_descr WHERE sha256Hash = ?''', (sha256Hash,)):
            return row["descr"]
        return False

    def mediaRate(self, sha256Hash, rate = None):
        if rate is not None:
            self._db.execute('''REPLACE INTO file_rating (sha256Hash, rating) VALUES (?, ?)''', (sha256Hash, rate,))
        for row in self._db.execute('''SELECT rating FROM file_rating WHERE sha256Hash = ?''', (sha256Hash,)):
            return row["rating"]
        return False
    
    def fetchKeyValue(self, key):
        for row in self._db.execute('''SELECT value FROM configs WHERE key = ?''', (key,)):
            try:
                if string.strip(row["value"]) == "":
                    return None
                return row["value"]
            except TypeError:
                raise NoExistKeyError(key)

    def setKeyValue(self, key, value):
        self._db.execute('''REPLACE INTO configs (key, value) VALUES (?, ?)''', (key, value,))
        self._db.commit()

    def countFileToSyncToS3(self):
        for row in self._db.execute('''SELECT count(f.sha256Hash) AS c FROM files f WHERE f.sha256Hash NOT IN (SELECT sha256Hash FROM s3)'''):
            return row["c"]

    def countFileSyncedToS3(self):
        for row in self._db.execute('''SELECT count(f.sha256Hash) AS c FROM files f WHERE f.sha256Hash IN (SELECT sha256Hash FROM s3)'''):
            return row["c"]

    def hashInS3(self, sha256Hash):
        for row in self._db.execute('''SELECT f.sha256Hash FROM files f WHERE f.sha256Hash IN (SELECT sha256Hash FROM s3)'''):
            if row["sha256Hash"] == sha256Hash:
                return True
        return False
    
    def fetchS3SyncList(self):
        try:
            syncList = []
            # Media
            for row in self._db.execute('''SELECT f.sha256Hash, f.filetype, fs.filesize FROM files f JOIN file_stats fs ON fs.sha256Hash = f.sha256Hash WHERE f.sha256Hash NOT IN (SELECT sha256Hash FROM s3)'''):
                syncList.append({"sha256Hash": row["sha256Hash"], "fileType": row["filetype"], "filesize": row["filesize"]})

            # Thumbnails
            for row in self._db.execute('''SELECT f.thumbSha256Hash AS sha256Hash, f.filesize FROM thumbnails f WHERE f.thumbSha256Hash NOT IN (SELECT sha256Hash FROM s3);'''):
                syncList.append({"sha256Hash": row["sha256Hash"], "fileType": "png", "filesize": row["filesize"]})
        except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
            raise OperationalError(e)
        except:
            raise ExecpectedError("Unexpected error:", sys.exc_info()[0])
        return syncList
    
    def fileSynced(self, sha256Hash, etag):
        self._db.execute('''INSERT INTO s3 (sha256hash, etag) VALUES (?, ?)''', (sha256Hash, etag,))
        self._db.commit()

    def getTagAndCountList(self):
        try:
            tagList = []
            for row in self._db.execute('''SELECT t.rowid, t.descr, (SELECT COUNT(*) FROM tagFileLink tl WHERE tl.tag = t.rowid) as tagUseCount FROM tags t ORDER BY t.descr'''):
                tagList.append({"id": row["rowid"], "tag": row["descr"], "useCount": row["tagUseCount"]})
        except (sqlite3.OperationalError, sqlite3.IntegrityError) as e:
            raise OperationalError(e)
        except:
            raise ExecpectedError("Unexpected error:", sys.exc_info()[0])
        return tagList
    
    def addTag(self, tag):
        try:
            self._db.execute('''INSERT INTO tags (descr) VALUES (?)''', (tag,))
            return True
        except:
            raise

    def rmTag(self, tag):
        try:
            self._db.execute('''DELETE FROM tagFileLink WHERE tag = (SELECT rowid FROM tags WHERE descr = ?)''', (tag,))
            self._db.execute('''DELETE FROM tags WHERE descr = ?''', (tag,))
            return True
        except:
            raise

    def rmTagByID(self, tagID):
        try:
            self._db.execute('''DELETE FROM tagFileLink WHERE tag = ?''', (tagID,))
            self._db.execute('''DELETE FROM tags WHERE rowid = ?''', (tagID,))
            return True
        except:
            raise

    def mediaAddTag(self, sha256Hash, tag):
        try:
            for row in self._db.execute('''SELECT count(rowid) AS c FROM tags WHERE descr = ?''', (tag,)):
                if int(row["c"]) != 1:
                    self.addTag(tag)
            self._db.execute('''INSERT INTO tagFileLink (tag, sha256Hash) VALUES ((SELECT rowid FROM tags WHERE descr = ?), ?)''', (tag, sha256Hash,))
            return True
        except:
            raise

    def mediaRMTag(self, sha256Hash, tag):
        try:
            self._db.execute('''DELETE FROM tagFileLink WHERE tag = (SELECT rowid FROM tags WHERE descr = ?) AND sha256Hash = ?''', (tag, sha256Hash,))
            return True
        except:
            raise

    def getTagID(self, tag):
        try:
            for row in self._db.execute('''SELECT rowid FROM tags WHERE descr = ?''', (tag,)):
                return row["rowid"]
        except:
            raise

    def imagesMissingThumbs(self):
        mediaList = []
        for row in self._db.execute("SELECT f.sha256Hash, s.filesize, f.fileType FROM files f JOIN file_stats s ON f.sha256Hash = s.sha256Hash WHERE f.mediaType = 'image' AND f.sha256Hash NOT IN (SELECT sha256Hash FROM thumbnails)"):
            mediaList.append(
                {"sha256Hash": row["sha256Hash"],
                 "fileType": row["fileType"],
                "filesize": row["filesize"]})
        return mediaList
